CREATE VIEW [dbo].[vSoaPartySummary]
AS  
SELECT  [Name].[ID] AS [PartyId],
        [Name].[ID] AS [Id],
        [Name].[STATUS] AS [Status],
        CASE WHEN [Name].[COMPANY_RECORD] = 1 THEN [Name].[COMPANY]
             ELSE [Name].[FULL_NAME]
        END AS [Name],
        CASE WHEN [Name].[COMPANY_RECORD] = 1 THEN [Name].[COMPANY_SORT]
             ELSE [Name].[LAST_FIRST]
        END AS [Sort],
        [Name].[CITY] AS [CityName],
        [Name].[STATE_PROVINCE] AS [CountrySubEntityName],
        [Name].[COUNTRY] AS [CountryName],
        CASE WHEN [Name].[WORK_PHONE] != '' THEN [Name].[WORK_PHONE]
             ELSE [Name].[HOME_PHONE]
        END AS [Phone],
        [Name].[EMAIL] AS [Email],
        CASE WHEN [Name].[STATUS] LIKE 'D%' THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsMarkedForDelete],
        [Name].[LAST_NAME] AS [LastName],
        [Name].[FIRST_NAME] AS [FirstName],
        [Name].[COMPANY] AS [OrganizationName],
        [Name].[DATE_ADDED] AS [CreatedOn],
        Null AS [CreatedBy],
        [Name].[LAST_UPDATED] AS [UpdatedOn],
        [Name].[UPDATED_BY] AS [UpdatedBy],
        [Name].[MEMBER_TYPE] AS [CustomerTypeCode]
  FROM [dbo].[Name]



GO